﻿using EasyDater.Base;
using EasyDater.Helper;
using System.Data;
using System.Text;

namespace EasyDater.Built
{
    public sealed class BuiltCore
    {
        #region  实体类操作表
        /// <summary>
        /// 向单独一个表中插入实体类
        /// </summary>
        /// <param name="mainTable">表名</param>        
        /// /// <param name="mainList">不带枚举类型SimInType(简化的插入数据类型)的List</param>   
        /// S: 字符串 N：整数 D：decimal数字 F：浮点数 A：日期
        /// BuiltEntity调用  不用从系统获取表结构
        /// <returns></returns>

        public string InsertTable(string mainTable, List<String[]> mainList)
        {
            string key = "";
            string value = "";
            int len = mainList.Count;
            string result;
            for (int i = 0; i < len; i++)
            {
                key += mainList[i][0].ToString();//键
                ////N是整数 D是decimal
                if (mainList[i][2].ToString() == "N")//类型
                    value += mainList[i][1].ToString();//值
                else if (mainList[i][2].ToString() == "D")
                    value += mainList[i][1].ToString();//值
                else if (mainList[i][2].ToString() == "A")//date 的第二个字母
                    value += "'" + mainList[i][1].ToString() + "'";//值
                else
                    value += "'" + mainList[i][1].Trim() + "'";
                if (i < len - 1)
                {
                    key += ",";
                    value += ",";
                }
            }
            string strMainSQL = "INSERT INTO " + mainTable + " (" + key + ") VALUES (" + value + ")";
            try
            {
                if (SQLHelper.ExecuteSql(strMainSQL))
                    result = "添加数据成功";
                else
                    result = "添加数据错误";
            }
            catch (Exception ex)
            {
                result = ex.Message;
            }
            return result;
        }
     
        /// <summary>
        /// 更新一个表中插入数据
        /// </summary>
        /// <param name="mainTable">表名</param>
        /// <param name="mainList">将要更新的数据 [key,value,type]</param>
        /// <param name="primaryKey">主键 [主键][值][类型]</param>
        /// S: 字符串 N：整数 D：decimal数字 F：浮点数 A：日期
        /// <returns></returns>

        public string UpdateTable(string mainTable, List<String[]> mainList, string[] primaryKey)
        {


            int len = mainList.Count;
            string result = "";
            string keyAndValue = "";
            for (int i = 0; i < len; i++)
            {
                string value = "";
                ////N是整数 D是decimal
                if (mainList[i][2].ToString() == "N")//类型
                    value += mainList[i][1].ToString();//值
                else if (mainList[i][2].ToString() == "D")
                    value += mainList[i][1].ToString();//值
                else if (mainList[i][2].ToString() == "A")//date 的第二个字母
                    value += "'" + mainList[i][1].ToString() + "'";//值
                else
                    value += "'" + mainList[i][1].Trim() + "'";

                keyAndValue += mainList[i][0].ToString();
                keyAndValue += "=";
                keyAndValue += value;
                if (i < (len - 1))
                {
                    keyAndValue += ",";
                }
            }


            string strMainSQL = "UPDATE " + mainTable + " SET " + keyAndValue + " WHERE " + primaryKey[0] + "=";
            if (primaryKey[2] == "N")
                strMainSQL += Int32.Parse(primaryKey[1]);
            else
                strMainSQL += "'" + StringUtility.HTMLEncode(primaryKey[1]) + "'";
            try
            {
                if (SQLHelper.ExecuteSql(strMainSQL))
                    result = "更新成功";
                else
                    result = "更新失败";
            }
            catch (Exception ex)
            {
                result = ex.Message;
            }
            return result;
        }
        #endregion


        #region SortedDictionary方式操作表

        //通过访问数据库系统表，获取表结构,组合SQL语句
        public string GetInsertSql(string tableName, SortedDictionary<string, string> sdList)
        {
            //获取表的结构,是什么数据类型，长度等
            DataTable dt = TableInfo.GetTableStruct(tableName);
            int itemCount = 0;//元素个数

            string field = "";//表的字段
            string fieldValue = "";//表的字段的值


            foreach (string key in sdList.Keys)
            {
                itemCount++;
                string thisValue = GetValue(dt, key, sdList);
                if (string.IsNullOrEmpty(thisValue)) continue;
                field += key;
                fieldValue += thisValue;
                // fieldValue += HttpUtility.HtmlEncode(thisValue);

                if (itemCount < sdList.Count)
                {
                    field += ",";
                    fieldValue += ",";
                }

            }
            string strMainSQL = "INSERT INTO " + tableName + " (" + field + ") VALUES (" + fieldValue + ");";

            return strMainSQL;
        }
        public string GetUpdateSql(string tableName, SortedDictionary<string, string> sdList)
        {
            DataTable dt = TableInfo.GetTableStruct(tableName);
            int len = sdList.Count;

            string condition = "";
            string priFiled = "";
            for (int t = 0; t < dt.Rows.Count; t++)
            {
                if (dt.Rows[t]["is_key"].ToString().Trim() == "1")
                {
                    priFiled = dt.Rows[t]["col_name"].ToString().Trim();
                    break;
                }
            }

            StringBuilder stringBuilder = new StringBuilder();
            string keyAndValue = "";
            foreach (string key in sdList.Keys)
            {
                string value = GetValue(dt, key, sdList);
                if (priFiled == key)
                {
                    condition = key + "=" + value;
                }
                else
                {
                    if (value == "null")
                        stringBuilder.Append(key + "=null,");
                    else
                        stringBuilder.Append(key + "=" + value + ",");
                    //keyAndValue += key;
                    //keyAndValue += "=";
                    //keyAndValue += value;
                    //keyAndValue += ",";
                }
            }
            keyAndValue = stringBuilder.ToString().TrimEnd();
            if (keyAndValue.EndsWith(",")) keyAndValue = keyAndValue.Substring(0, keyAndValue.Length - 1);
            return "UPDATE " + tableName + " SET " + keyAndValue + " WHERE  " + condition + ";";
        }
        /// <summary>
        /// 对字段进行格式化
        /// </summary>
        /// <param name="dt">该表所有字段的类型、长度等</param>
        /// <param name="key">该字段</param>
        /// <param name="sdList">传入的数据列表</param>
        /// <returns></returns>
        private string GetValue(DataTable dt, string key, SortedDictionary<string, string> sdList)
        {
            string fType = "";
            int fLength = 0;
            for (int t = 0; t < dt.Rows.Count; t++)
            {
                if (dt.Rows[t]["col_name"].ToString().Trim() == key) //注意：字段不包括 [],所以定义表的时候，不要使用关键字
                {
                    fType = dt.Rows[t]["col_type"].ToString();
                    fLength = int.Parse(dt.Rows[t]["col_len"].ToString());
                }
            }
            string theValues = sdList[key];
            if (theValues == "null" || theValues == "") return "null";
            string returnValue = "";//返回的字串
            switch (fType)
            {
                case "int":
                case "float":
                case "tinyint":
                case "decimal":
                case "bit":
                case "bigint":
                case "smallint":
                case "money":
                case "smallmoney":
                case "real":
                case "numeric":

                    if (string.IsNullOrWhiteSpace(theValues))
                    {
                        theValues = "0";
                    }
                    else
                    {
                        try
                        {
                            double tmp = double.Parse(theValues);
                        }
                        catch (Exception e)
                        {
                            //LogHelper.WriteLog("输入的数据应该为数据: " + theValues, e);
                            throw new Exception(e.Message);
                        }
                    }
                    returnValue = theValues;

                    break;
                case "datetime":
                case "datetime2":
                case "smalldatetime":
                case "timestamp":
                    returnValue = "'" + theValues + "'";
                    break;
                case "uniqueidentifier":
                    returnValue = "'" + theValues + "'";
                    break;
                default:
                    if (string.IsNullOrWhiteSpace(theValues))
                        returnValue += "'" + theValues + "'";
                    else if (fLength == -1)
                        returnValue += "'" + theValues + "'";
                    else if (theValues.Length > fLength)
                        returnValue = "'" + theValues.Substring(0, fLength) + "'";
                    else
                        returnValue += "'" + theValues + "'";
                    break;
            }

            return returnValue;
        }

        //判断一列 是 S 还是 N
        public string GetColType(string tableName, string colName)
        {
            string colType = "";
            DataTable dt = TableInfo.GetTableStruct(tableName);
            if (dt == null) return "";
            if (dt.Rows.Count < 1) return "";

            for (int j = 0; j < dt.Rows.Count; j++)
            {
                if (dt.Rows[j]["col_name"].ToString().ToUpper() == colName.ToUpper())
                {
                    colType = dt.Rows[j]["col_type"].ToString();
                    break;
                }
            }

            string returnValue = "";//返回的字串
            switch (colType)
            {
                case "int":
                case "float":
                case "tinyint":
                case "decimal":
                case "bit":
                case "bigint":
                case "smallint":
                case "money":
                case "smallmoney":
                case "real":
                case "numeric":
                    returnValue = "N";
                    break;
                case "datetime":
                case "datetime2":
                case "smalldatetime":
                case "timestamp":
                    returnValue = "S";
                    break;
                case "uniqueidentifier":
                    returnValue = "S";
                    break;
                default:
                    returnValue = "S";
                    break;
            }

            return returnValue;
        }
        #endregion
        #region 实体类
        public string GetPropInsertSQL<T>(T t)
        {
            Type clsType = typeof(T); 
            string tableName = clsType.Name;//类名就是表名

            List<string[]> mainList = GetProperties(t);
            string key = "";
            string value = "";
            int len = mainList.Count;

            for (int i = 0; i < len; i++)
            {
                key += mainList[i][0].ToString();//键
                value += GetValue(mainList[i]);
                if (i < len - 1)
                {
                    key += ",";
                    value += ",";
                }
            }
            string sql = "INSERT INTO " + tableName + " (" + key + ") VALUES (" + value + ");";
            return sql;
        }

        public string GetPropUpdateSQL(object obj, string tableName)
        {
            DataTable dt = TableInfo.GetTableStruct(tableName);
            string priFiled = "";
            for (int t = 0; t < dt.Rows.Count; t++)
            {
                if (dt.Rows[t]["is_key"].ToString().Trim() == "1")
                {
                    priFiled = dt.Rows[t]["col_name"].ToString().Trim();
                    break;
                }
            }
            StringBuilder stringBuilder = new StringBuilder();
            string keyAndValue = "";
            string condition = "";
            List<string[]> mainList = GetProperties(obj);
            int len = mainList.Count;
            for (int i = 0; i < len; i++)
            {
                string value = GetValue(mainList[i]);
                if (priFiled == mainList[i][0].ToString())
                {
                    condition = mainList[i][0].ToString() + "=" + value;
                }
                else
                {
                    if (value == "null")
                        stringBuilder.Append(mainList[i][0].ToString() + "=null,");
                    else
                        stringBuilder.Append(mainList[i][0].ToString() + "=" + value + ",");
                }
            }
            keyAndValue = stringBuilder.ToString().TrimEnd();
            if (keyAndValue.EndsWith(",")) keyAndValue = keyAndValue.Substring(0, keyAndValue.Length - 1);
            return "UPDATE " + tableName + " SET " + keyAndValue + " WHERE  " + condition + ";";
        }
        private string GetValue(string[] item)
        {
            string value = "";
            switch (item[2].ToString())
            {
                case "DateTime":
                case "DateTime2":
                    value = "'" + item[1].ToString() + "'";
                    break;
                case "String":
                case "Guid":
                    value = "'" + StringUtility.SQLInjection(item[1].ToString()) + "'";
                    break;
                case "BigInt":
                case "Int32":
                case "UInt32":
                case "Int64":
                case "UInt64":
                case "Byte":
                case "SByte":
                case "Int16":
                case "UInt16":
                case "Double":
                case "Decimal":
                case "money":
                    value = item[1].ToString();//值
                    break;
                default:
                    value = "'" + StringUtility.SQLInjection(item[1].Trim()) + "'";
                    break;
            }
            return value;
        }

        //根据实体类中定义字段的数据类型，转换为枚举，组合SQL语句的时候使用
        public string GetEnumValue(string dbType)
        {
            string value = "";
            switch (dbType)
            {
                case "DateTime":
                case "DateTime2":
                    value = global::EasyDater.Base.EnumType.SimInType.A.ToString();
                    break;
                case "String":
                case "Guid":
                    value = global::EasyDater.Base.EnumType.SimInType.S.ToString();
                    break;
                case "Int32":
                case "UInt32":
                case "Int64":
                case "UInt64":
                case "Byte":
                case "SByte":
                case "Int16":
                case "UInt16":
                    value = global::EasyDater.Base.EnumType.SimInType.N.ToString();
                    break;
                case "Double":
                case "money":
                    value = global::EasyDater.Base.EnumType.SimInType.F.ToString();
                    break;
                case "Decimal":
                    value = global::EasyDater.Base.EnumType.SimInType.D.ToString();//值
                    break;
                default:
                    value = global::EasyDater.Base.EnumType.SimInType.S.ToString();
                    break;
            }
            return value;
        }
        
        //返回  name, value, dType 列表
        public List<string[]> GetProperties<T>(T t)
        {
            List<string[]> list = new List<string[]>();
            if (t is null)
            {
                return list;
            }
            //实例或公共成员
            System.Reflection.PropertyInfo[] properties = t.GetType().GetProperties(System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.Public);

            if (properties.Length <= 0)
            {
                return list;
            }
            foreach (System.Reflection.PropertyInfo item in properties)
            {
                string name = item.Name;
                object? value = item.GetValue(t, null);
                string dType = item.PropertyType.Name;
               
                if (item.PropertyType.IsValueType || item.PropertyType.Name.StartsWith("String"))
                {
                    list.Add(new string[] { name, value.ToString(), dType });
                }
               // else
                //{
                    GetProperties(value);
               // }
            }
            return list;
        }

        #endregion

      
    }
}
